const express = require('express');
const router = express.Router();
const db = require('../../../../util/dbConfig/dbConfig')
// const dateReplace = require('../../../../public/javascripts/pubilc')

router.post('/',async (req,res) => {
    let {
        sheepId,    //羊只编号
        specimen,   //样品编号
        diameterMax,   //直径最大值
        diameterMin,    //直径最小值
        diameter_differenceMax, //直径标准差最大值
        diameter_differenceMin, //直径标准差最小值
        lengthMax,     //长度最大值
        lengthMin,      //长度最小值
        length_lengthMax,  //长度标准差最大值
        length_lengthMin,  //长度标准差最小值
        length_codefficientMax,     //长度变异系数最大值
        length_codefficientMin,       //长度变异系数最小值
        measurement_number,     //测量数
        measurement_date,       //测量日期
        sortLowNum,   //第几列的排序数，从左往右，从1开始
        ISAsc,         //增还是减  1为增，0为减
        page,      //页码
        isPage,     //是否分页 1为分页0为不分页
    }=req.body
    //没有页码返回错误
    if (!page) {
        return res.send({
            code: 1,
            msg: '参数错误，请输入页码'
        })
  }

    let newPage = (page - 1) * 10 //跳过多少页
    let arr = [
        specimen,   //样品编号
        diameterMax,   //直径最大值
        diameterMin,    //直径最小值
        diameter_differenceMax, //直径标准差最大值
        diameter_differenceMin, //直径标准差最小值
        lengthMax,     //长度最大值
        lengthMin,      //长度最小值
        length_lengthMax,  //长度标准差最大值
        length_lengthMin,  //长度标准差最小值
        length_codefficientMax,     //长度变异系数最大值
        length_codefficientMin,       //长度变异系数最小值
        measurement_number,     //测量数
        measurement_date,       //测量日期
    ]

    //模糊查询条件有一个即可
    let cateBool = specimen || diameterMax || diameterMin || diameter_differenceMax ||
        diameter_differenceMin || lengthMax || lengthMin || length_lengthMax || length_lengthMin
        || length_codefficientMax || length_codefficientMin || measurement_number || measurement_date

    let sheepAndCateBoll = sheepId && cateBool //羊只编号与模糊查询条件是否都同时存在，有羊只编号就没有模糊查询条件
  for (let i = 0; i < arr.length; i++){
    console.log(arr[i],arr[i] === '')
    if (arr[i] === '') {

      arr[i] === null
    }
  }
    if(!sheepId && !cateBool ){
        return res.send({
            code: 1,
            msg: '参数错误'
        })
    }
    if(sheepAndCateBoll){
        return res.send({
            code: 1,
            msg: '参数错误，不可以精确查询与条件查询并行查询'
        })
    }
    // //如果是编号查询
    // if(measurement_date){
    //     //时间格式修改
    //     await dateReplace(measurement_date,res)
    // }
    //获取当前用户权限以及id
    let userId = req.session.userInfo._id
    let userAdmin = req.session.userInfo.admin
    try{
        let sql
        let findSql
        let sqlArr
        //管理员权限
        if(userAdmin === 1){
             findSql = `select ID from sheep where ID=?`
             sqlArr = [sheepId]
             sql = `select * from sheepftne 
        where 
        SPECIMEN=?
        or DIAMETER<=?
        and DIAMETER>=?
        or D_DIFFERENCE<=?
        and D_DIFFERENCE>=?
        or LENGTH<=?
        and LENGTH>=?
        or L_DIFFERENCE<=?
        and L_DIFFERENCE>=?
        or L_CODEFFICIENT<=?
        and L_CODEFFICIENT>=?
        or MEASUREMENT_NUMBER<=?
        and MEASUREMENT_NUMBER>=?
        and YEAR(MEASUREMENT_DATE)=?
        and SHEEP_ID=?
        `
        }
        else if(userAdmin === 0){
            findSql = `select ID from sheep where ID=? and USER_ID=?`
            sqlArr = [sheepId,userId]
            sql = `select 
             a.ID,
             a.SHEEP_ID,
             a.SPECIMEN,
             a.DIAMETER,
             a.D_DIFFERENCE,
             a.LENGTH,
             a.L_DIFFERENCE,
             a.L_CODEFFICIENT,
             a.MEASUREMENT_NUMBER,
             a.MEASUREMENT_DATE,
             a.REMARK
             from sheepftne a,sheep b  where a.SHEEP_ID=b.ID and b.USER_ID=${userId}
        or a.SPECIMEN=?
        or a.DIAMETER<=?
        and a.DIAMETER>=?
        or a.D_DIFFERENCE<=?
        and a.D_DIFFERENCE>=?
        or a.LENGTH<=?
        and a.LENGTH>=?
        or a.L_DIFFERENCE<=?
        and a.L_DIFFERENCE>=?
        or a.L_CODEFFICIENT<=?
        and a.L_CODEFFICIENT>=?
        or a.MEASUREMENT_NUMBER=?
        and YEAR(a.MEASUREMENT_DATE)=?
        and a.SHEEP_ID=?
        `
        }
        if(sheepId){
            //羊只编号查询
            //查询是否有该羊
            let result1 = await db.SySqlConnect(findSql,sqlArr)
            if(result1.length === 0){
                return res.send({
                    code: 1,
                    msg: '无该羊编号'
                })
            }
            //sql拼接
            let index = sql.indexOf('where')
            //判断用户权限
            if(userAdmin === 1){
                sql = sql.slice(0,index) + `where SHEEP_ID=?`
            }else if(userAdmin === 0){
                sql = sql.slice(0,index) + `where a.SHEEP_ID=b.ID and b.USER_ID='${userId}' and a.SHEEP_ID=?`
            }
            //查询总结果条数的sql语句
            let countSql = `SELECT count(*) FROM  (${sql}) as d `
            //排序查询
            if(sortLowNum !== null && sortLowNum !== '' && ISAsc !== null && ISAsc !== ''){
                if(ISAsc === 1){
                    //升序
                    sql = sql + `order by ${sortLowNum} asc`
                }else if(ISAsc === 0){
                    //降序
                    sql = sql + `order by ${sortLowNum} desc`
                }
            }
            //分页sql
            if(isPage === 1){
                sql = sql + ' ' +`limit ${newPage},10`
            }
            //分页查询结果
          let result2 = await db.SySqlConnect(sql, arr)
          console.log(sql)
            //结果总条数
            let countAll = await db.SySqlConnect(countSql,arr)
            countAll = countAll[0]['count(*)']
            return res.send({
                code: 0,
                msg: '查询成功',
                date: {
                    countAll,
                    length: result2.length,
                    result2
                }
            })
        }
        //模糊查询
        let index = sql.lastIndexOf('and')
        sql = sql.slice(0,index)
        let countSql = `SELECT count(*) FROM  (${sql}) as d`
        //排序
        if(sortLowNum && ISAsc){
            if(ISAsc === 1){
                //升序
                sql = sql + `order by ${sortLowNum} asc`
            }else{
                //降序
                sql = sql + `order by ${sortLowNum} desc`
            }
        }
        //分页sql语句
        sql = sql + `limit ${newPage},10`
        //分页查询结果
      console.log(arr)
      let result2 = await db.SySqlConnect(sql, arr)
      console.log(sql)
        //结果总条数


        let countAll = await db.SySqlConnect(countSql,arr)
        countAll = countAll[0]['count(*)']
        return res.send({
            code: 0,
            msg: '查询成功',
            date: {
                countAll,
                length: result2.length,
                result2
            }
        })

    }catch (e) {
        console.log(e);
        return res.send({
            code: 4,
            msg: '服务器错误，请稍后再试'
        })
    }
})

module.exports = router
